﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Globalization;

namespace BSC_OTC_THONGBAO
{
    class ProcessDB
    {
        private Code.CL_Ultility _cl = new Code.CL_Ultility();
        Connect _connect = new Connect();
        public DataTable GetAllJobInToday()
        {
            try
            {
                DataTable result = new DataTable("bsc_tbl_congviec");
                string today = DateTime.Now.ToShortDateString();
                _connect._cmd.CommandText = "select cv.*,hd.tenhd,tp.trangthai from bsc_tbl_congviec cv, bsc_tbl_hopdongdaily hd, bsc_tbl_tpph tp " +
                                             "where tgcanhbao = TO_DATE(:tgcanhbao,'dd/MM/yyyy') and cv.sohd=hd.sohd " +
                                             "and cv.duyet=1 " +
                                             "and tp.matp = cv.matp " +
                                             "and tp.trangthai=0";
                //_connect._cmd.CommandText = "select * from bsc_tbl_congviec cv, bsc_tbl_hopdongdaily hd where tgcanhbao = :tgcanhbao and cv.sohd=hd.sohd and cv.duyet=1";
                _connect._cmd.Parameters.Clear();
                _connect._cmd.Parameters.Add(":tgcanhbao", System.Data.OracleClient.OracleType.DateTime).Value = DateTime.ParseExact(today, "dd/MM/yyyy", CultureInfo.InvariantCulture);//today;
                _connect.OpenConnect();
                _connect._da = new System.Data.OracleClient.OracleDataAdapter(_connect._cmd);
                _connect._da.Fill(result);
                _connect.CloseConnect();
                return result;
            }
            catch(Exception ex){
                _cl.UPDATE_LOG_FILE("ERR", "", "GetAllJobInToday()", "GetAllJobInToday()", ex.Message);
                return null;
            }
        }

        public void UpdateToEmailLog(string _loaiEmail,string _ghichu)
        {
            try
            {
            _connect._cmd.CommandText = "insert into bsc_emaillog (ngaygui,giogui,dagui,loaiemail,ghichu) values(TO_DATE(:ngaygui,'dd/MM/yyyy'),:giogui,:dagui,:loaiemail,:ghichu)";
            _connect._cmd.Parameters.Clear();
            _connect._cmd.Parameters.Add(":ngaygui", System.Data.OracleClient.OracleType.DateTime).Value = DateTime.Now.ToString("dd/MM/yyyy");
            _connect._cmd.Parameters.Add(":giogui", System.Data.OracleClient.OracleType.VarChar).Value = DateTime.Now.ToLongTimeString();
            _connect._cmd.Parameters.Add(":dagui", System.Data.OracleClient.OracleType.Int32).Value = 1;
            _connect._cmd.Parameters.Add(":loaiemail", System.Data.OracleClient.OracleType.VarChar).Value = _loaiEmail;
            _connect._cmd.Parameters.Add(":ghichu", System.Data.OracleClient.OracleType.VarChar).Value = _ghichu;
            _connect.OpenConnect();
            _connect._cmd.ExecuteNonQuery();
            _connect.CloseConnect();
            }
            catch(Exception ex){
                _cl.UPDATE_LOG_FILE("ERR", "", "UpdateToEmailLog()", "UpdateToEmailLog()", ex.Message);
            }
        }

        public bool CheckSendedEmailToDay()
        {
            try
            {
                DataTable result = new DataTable("bsc_emaillog");
                string today = DateTime.Now.ToString("dd/MM/yyyy");
                _connect._cmd.CommandText = "select * from bsc_emaillog where ngaygui = TO_DATE(:ngaygui,'dd/MM/yyyy') and dagui=1";
                _connect._cmd.Parameters.Clear();
                _connect._cmd.Parameters.Add(":ngaygui", System.Data.OracleClient.OracleType.DateTime).Value = DateTime.ParseExact(today, "dd/MM/yyyy", CultureInfo.InvariantCulture).ToString("dd/MM/yyyy");
                _connect.OpenConnect();
                _connect._da = new System.Data.OracleClient.OracleDataAdapter(_connect._cmd);
                _connect._da.Fill(result);
                _connect.CloseConnect();
                if (result.Rows.Count > 0)
                {
                    return false;
                }
                return true;
            }
            catch (Exception ex)
            {
                _cl.UPDATE_LOG_FILE("ERR", "", "CheckSendedEmailToDay()", "CheckSendedEmailToDay()", ex.Message);
                return false;
            }
        }
    }
}
